const article = {
  async GetList(data) {
    let whereConditions = [];
    let queryParams = [];

    if (data.title) {
      whereConditions.push("title LIKE ?");
      queryParams.push(`%${data.title}%`);
    }
    if (data.is_delete !== undefined) {
      whereConditions.push("is_delete = ?");
      queryParams.push(+data.is_delete);
    }
    if (data.status !== undefined && +data.status !== 0) {
      whereConditions.push("status = ?");
      queryParams.push(+data.status);
    }
    if (data.category_id !== undefined && +data.category_id !== 0) {
      whereConditions.push("category_id = ?");
      queryParams.push(+data.category_id);
    }
    if (data.type !== undefined && +data.type !== 0) {
      whereConditions.push("type = ?");
      queryParams.push(+data.type);
    }
    if (data.tag_id !== undefined && data.tag_id !== 0) {
      whereConditions.push("tag_id = ?");
      queryParams.push(data.tag_id);
    }

    //将条件拼接
    const whereClause =
      whereConditions.length > 0
        ? "WHERE " + whereConditions.join(" AND ")
        : "";

    const query = `
        Select article.*,category.name, GROUP_CONCAT(tag.name) AS tag_names
        FROM article
        LEFT JOIN category ON article.category_id = category.id
        LEFT JOIN article_tag ON article.id = article_tag.article_id
        LEFT JOIN tag ON article_tag.tag_id = tag.id
        ${whereClause}
        GROUP BY article.id
        ORDER BY article.is_top DESC, article.id DESC
        LIMIT ? OFFSET ?
        `;

    const [results] = await global.mysqlPool.execute(query, [
      ...queryParams,
      data.page_size,
      data.page_size * (data.page_num - 1),
    ]);

    const countQuery = `
        Select Count(*) AS total
        from article
        left join article_tag on article.id = article_tag.article_id
        left join tag on article_tag.tag_id =tag.id
        ${whereClause}
        group by article.id
        `;
    const [countResults] = await global.mysqlPool.execute(
      countQuery,
      queryParams
    );
    return {
      total: countResults.length,
      articleList: results,
    };
  },
  async GetFrontList(parmas) {
    const page_size = parmas.page_size;
    const page_num = parmas.page_num;
    const category_id = parmas.category_id;
    const tag_id = parmas.tag_id;
    let query = `SELECT article.id, article.title, article.content, article.img, article.type, 
    article.is_top, article.created_at, article.category_id, 
    category.name as category_name,
    GROUP_CONCAT(tag.name) AS tags
    FROM article
    LEFT JOIN category ON article.category_id = category.id
    LEFT JOIN article_tag ON article.id = article_tag.article_id
    LEFT JOIN tag ON article_tag.tag_id = tag.id
    WHERE article.is_delete = 0 AND article.status = 1`;

    if (category_id !== undefined && +category_id !== 0) {
      query += ` AND article.category_id = ${+category_id}`;
    }

    if (tag_id !== undefined && +tag_id !== 0) {
      query += ` AND article.id IN (SELECT article_id FROM article_tag WHERE tag_id = ${+tag_id})`;
    }

    query += ` GROUP BY article.id, article.title, article.content, article.img, article.type, 
    article.is_top, article.created_at, article.category_id, category.name`;

    const [rows, fields] = await global.mysqlPool.execute(query);

    let total = rows.length ?? 0;
    if (page_size !== undefined && page_num !== undefined) {
      query += ` ORDER BY is_top DESC, id DESC
               LIMIT ${page_size} OFFSET ${page_size * (page_num - 1)}`;
    }

    const [result, _] = await global.mysqlPool.execute(query);
    let list = result;

    list = list.map((item) => {
      item.tags = item.tags ? item.tags.split(",") : [];
      return item;
    });

    return {
      list,
      total,
    };
  },
  async GetInfoById(id) {
    let query = `SELECT article.id, article.title, article.content, article.img, article.type, 
    article.is_top, article.created_at, article.category_id, article.updated_at, 
    category.name as category_name,
    GROUP_CONCAT(tag.name) AS tags
    FROM article
    LEFT JOIN category ON article.category_id = category.id
    LEFT JOIN article_tag ON article.id = article_tag.article_id
    LEFT JOIN tag ON article_tag.tag_id = tag.id
    WHERE article.id = ${id} AND article.is_delete = 0 AND article.status = 1`;
    const [row] = await global.mysqlPool.query(query);
    return row[0];
  },
  async GetRecommendList(id, n) {
    const [rows] = await global.mysqlPool.execute(
      `SELECT DISTINCT t2.id, t2.title, t2.img, t2.created_at, t2.is_top FROM
        (SELECT DISTINCT t1.article_id FROM
          (SELECT tag_id FROM article_tag WHERE article_id = ?) t
          JOIN article_tag t1 ON t.tag_id = t1.tag_id
          WHERE t1.article_id != ?) sub2
        JOIN article t2 ON sub2.article_id = t2.id
        WHERE t2.is_delete = 0
        ORDER BY t2.is_top, t2.id DESC
        LIMIT ?`,
      [id, id, n]
    );

    const recommendArticleList = rows.map((row) => ({
      id: row.id,
      title: row.title,
      img: row.img,
      created_at: row.created_at,
    }));

    return recommendArticleList;
  },
  async GetNewestList(n) {
    const [rows] = await global.mysqlPool.execute(
      `SELECT id, title, img, created_at FROM article
       WHERE is_delete = 0 AND status = 1
       ORDER BY created_at DESC, id ASC
       LIMIT ?`,
      [n]
    );

    const newestList = rows.map((row) => ({
      id: row.id,
      title: row.title,
      img: row.img,
      created_at: row.created_at,
    }));
    return newestList;
  },
  async GetLast(id) {
    const [lastArticleRow] = await global.mysqlPool.execute(
      `SELECT id, title, img FROM article
       WHERE is_delete = 0 AND status = 1 AND id = (
         SELECT max(id) FROM article WHERE id < ?
       )
       LIMIT 1`,
      [id]
    );

    if (lastArticleRow.length === 0) {
      return {}; // 没有找到上一篇文章
    }

    const lastArticle = {
      id: lastArticleRow[0].id,
      title: lastArticleRow[0].title,
      img: lastArticleRow[0].img,
    };

    return lastArticle;
  },
  async GetNext(id) {
    const [lastArticleRow] = await global.mysqlPool.execute(
      `SELECT id, title, img FROM article
      WHERE is_delete = 0 AND status = 1 AND id > ?
      LIMIT 1`,
      [id]
    );

    if (lastArticleRow.length === 0) {
      return {}; // 没有找到上一篇文章
    }

    const lastArticle = {
      id: lastArticleRow[0].id,
      title: lastArticleRow[0].title,
      img: lastArticleRow[0].img,
    };

    return lastArticle;
  },
};

module.exports = article;
